from numpy.core.defchararray import lower

from Modules.mod_check_encoding import CheckDetectEncode
from Modules.mod_psql import PSQLDB
from Modules.mod_logging import Logging

import csv
import os


class ImportFromDB:

    def __init__(self, name_schema: str):
        self.schema: str = name_schema

    # Парсинг csv и отправка в creating_record_request
    def parse_csv(self, path_csv_files: str) -> None:
        keys: dict = self.get_keys()
        for file_name in os.listdir(path_csv_files):
            path_csv_file: str = f'{path_csv_files}/{file_name}'
            coding: str = CheckDetectEncode().detector_file_encoding(path_csv_file)
            with open(path_csv_file, newline='', encoding=coding) as File:
                reader = csv.reader(File)
                self.creating_record_request(list(reader), file_name.replace('.csv', ''), keys[f"{lower(self.schema)}.{file_name.replace('.csv', '')}"])

    def creating_record_request(self, datas: list, name_table: str, p_k: list) -> None:
        con = PSQLDB().connection()
        # Столбцы таблицы
        header: list = datas[0][0].split(';')[1:]
        # Значения
        values: str = ''
        querys: list = []
        # Исключения
        exclude: str = ', '.join(['EXCLUDED.' + h for h in header])
        # Запрос к бд
        for val in range(1, len(datas) - 1):
            values_list: list = datas[val][0].split(';')[1:]
            values: str = f'''('{"', '".join(values_list)}')'''
            query: str = f"""insert into {self.schema}.{name_table}({', '.join(header)})
                values
                {values}
                on conflict({', '.join(p_k)})
                do update set({', '.join(header)}) = ({exclude});""".replace("''", "null")
            err = PSQLDB().insert(connect=con, sql=query)
            if err == 0:
                Logging().set_log(con, f"{self.schema}.{name_table};{values}", 'inserted', 'LOGS')
                Logging().write_log_to_file(f"inserted: {self.schema}.{name_table}: {values}", 'info')
            else:
                Logging().set_log(con, f"{err}: {self.schema}.{name_table};{values}", 'error', 'LOGS')
                Logging().write_log_to_file(f"{err}: {self.schema}.{name_table}: {values}", 'error')
        PSQLDB().exit_db(con)

    # Получения ключей исключений из таблиц
    def get_keys(self) -> dict:
        primary_keys: dict = {}
        sql: str = f"""SELECT
                            conrelid::regclass AS table_name,
                            pg_get_constraintdef(oid)
                        FROM
                            pg_constraint
                        WHERE
                            contype = 'p' AND
                            connamespace = '{self.schema}'::regnamespace
                        ORDER BY
                            conrelid::regclass::text, contype DESC
                    """
        keys = PSQLDB().select_db(sql)
        for k in keys:
            primary_keys[k[0]] = k[1].replace('PRIMARY KEY (', '').replace(')', '').split(', ')
        return primary_keys